package org.example.dbcompare.service;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import lombok.extern.slf4j.Slf4j;
import org.example.dbcompare.model.*;
import org.example.dbcompare.model.config.ExportSqlConfig;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 导出缺失SQL服务
 *
 * @author wuyong
 * @date 2025/10/22
 */
@Slf4j
public class ExportMissSqlService {

    public ExportMissSqlService() {
    }

    /**
     * 导出缺失的SQL语句
     *
     * @param compareContext 对比上下文
     * @param compareResult  对比结果
     * @param config         导出配置
     * @return 导出的SQL结果
     */
    public ExportSqlResult exportMissSql(CompareContext compareContext, CompareResult compareResult, ExportSqlConfig config) {
        log.info("开始导出缺失的SQL语句");

        ExportSqlResult exportSqlResult = new ExportSqlResult();

        // 创建源数据库数据源
        SimpleDataSource sourceDataSource = new SimpleDataSource(
                compareContext.getSourceDb().getUrl(),
                compareContext.getSourceDb().getUsername(),
                compareContext.getSourceDb().getPassword()
        );

        try {
            // 1. 导出缺失表的CREATE语句
            if (config.isExportTable()) {
                exportMissingTables2(sourceDataSource, compareResult, exportSqlResult, config);
            }

            // 2. 导出缺失列的ALTER语句
            if (config.isExportMissingColumn()) {
                exportMissingColumns(sourceDataSource, compareResult, exportSqlResult, config);
            }

            // 3. 导出列类型不匹配的ALTER语句
            if (config.isExportDifferentColumn()) {
                exportColumnMismatches(sourceDataSource, compareResult, exportSqlResult, config);
            }

        } catch (Exception e) {
            log.error("导出SQL语句时发生错误: {}", e.getMessage(), e);
        }

        log.info("SQL导出完成，共生成 {} 条SQL语句", exportSqlResult.getAllSqls().size());
        return exportSqlResult;
    }

    private void exportMissingTables2(SimpleDataSource sourceDataSource, CompareResult compareResult,
                                      ExportSqlResult exportSqlResult, ExportSqlConfig config) {
        Set<String> missingTables = compareResult.getMissingTables();
        if (missingTables == null || missingTables.isEmpty()) {
            log.info("没有缺失的表");
            return;
        }

        log.info("开始导出 {} 个缺失表的CREATE语句", missingTables.size());

        for (String tableName : missingTables) {
            if (isTableIgnored(tableName, config)) {
                log.info("表 {} 命中忽略规则，跳过导出CREATE语句", tableName);
                continue;
            }
            try {
                Entity entity = Db.use(sourceDataSource).queryOne("show create table " + tableName);
                String createSql = entity.getStr("Create Table");
                if (createSql != null) {
                    createSql = createSql.trim();
                    if (!createSql.endsWith(";")) {
                        createSql = createSql + ";";
                    }
                    exportSqlResult.addCreateTableSql(createSql);
                }
            } catch (Exception e) {
                log.error("生成表 {} 的CREATE语句时发生错误: {}", tableName, e.getMessage(), e);
            }
        }
    }

    /**
     * 导出缺失列的ALTER语句
     */
    private void exportMissingColumns(SimpleDataSource sourceDataSource, CompareResult compareResult,
                                      ExportSqlResult exportSqlResult, ExportSqlConfig config) {
        Map<String, Set<String>> missingColumns = compareResult.getMissingColumns();
        if (missingColumns == null || missingColumns.isEmpty()) {
            log.info("没有缺失的列");
            return;
        }

        log.info("开始导出缺失列的ALTER语句");

        for (Map.Entry<String, Set<String>> entry : missingColumns.entrySet()) {
            String tableName = entry.getKey();
            Set<String> columns = entry.getValue();

            if (isTableIgnored(tableName, config)) {
                log.info("表 {} 命中忽略规则，跳过导出缺失列ALTER语句", tableName);
                continue;
            }

            for (String columnName : columns) {
                try {
                    String alterSql = generateAddColumnSql(sourceDataSource, tableName, columnName, config);
                    if (alterSql != null) {
                        exportSqlResult.addAddColumnSql(alterSql);
                        log.info("已生成表 {} 列 {} 的ADD语句", tableName, columnName);
                    }
                } catch (Exception e) {
                    log.error("生成表 {} 列 {} 的ADD语句时发生错误: {}", tableName, columnName, e.getMessage(), e);
                }
            }
        }
    }

    /**
     * 导出列类型不匹配的ALTER语句
     */
    private void exportColumnMismatches(SimpleDataSource sourceDataSource, CompareResult compareResult,
                                        ExportSqlResult exportSqlResult, ExportSqlConfig config) {
        Map<String, Set<ColumnMismatch>> columnTypeMismatches = compareResult.getColumnTypeMismatches();
        if (columnTypeMismatches == null || columnTypeMismatches.isEmpty()) {
            log.info("没有列类型不匹配的情况");
            return;
        }

        log.info("开始导出列类型不匹配的ALTER语句");

        for (Map.Entry<String, Set<ColumnMismatch>> entry : columnTypeMismatches.entrySet()) {
            String tableName = entry.getKey();
            Set<ColumnMismatch> mismatches = entry.getValue();

            if (isTableIgnored(tableName, config)) {
                log.info("表 {} 命中忽略规则，跳过导出类型不匹配ALTER语句", tableName);
                continue;
            }

            for (ColumnMismatch mismatch : mismatches) {
                try {
                    String alterSql = generateModifyColumnSql(sourceDataSource, tableName, mismatch, config);
                    if (alterSql != null) {
                        exportSqlResult.addAlterColumnSql(alterSql);
                        log.info("已生成表 {} 列 {} 的MODIFY语句", tableName, mismatch.getColumnName());
                    }
                } catch (Exception e) {
                    log.error("生成表 {} 列 {} 的MODIFY语句时发生错误: {}", tableName, mismatch.getColumnName(), e.getMessage(), e);
                }
            }
        }
    }

    /**
     * 判断表是否命中忽略规则：在 ignoreTables 中，或匹配 ignoreTablePatterns
     */
    private boolean isTableIgnored(String tableName, ExportSqlConfig config) {
        if (tableName == null || config == null) {
            return false;
        }
        List<String> ignoreTables = config.getIgnoreTables();
        if (ignoreTables != null && ignoreTables.stream().anyMatch(t -> tableName.equalsIgnoreCase(t))) {
            return true;
        }
        List<String> patterns = config.getIgnoreTablePatterns();
        if (patterns != null) {
            for (String pattern : patterns) {
                if (pattern == null || pattern.isEmpty()) {
                    continue;
                }
                String regex = globToRegex(pattern);
                if (tableName.matches(regex)) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 简单通配符转正则：* -> .*, ? -> .
     */
    private String globToRegex(String glob) {
        StringBuilder sb = new StringBuilder();
        // 不区分大小写
        sb.append("(?i)^");
        for (int i = 0; i < glob.length(); i++) {
            char c = glob.charAt(i);
            switch (c) {
                case '*': sb.append(".*"); break;
                case '?': sb.append("."); break;
                case '.': sb.append("\\."); break;
                case '(': case ')': case '+': case '|': case '^': case '$': case '{': case '}': case '[': case ']': case '\\':
                    sb.append('\\').append(c); break;
                default: sb.append(c);
            }
        }
        sb.append("$");
        return sb.toString();
    }

    /**
     * 生成添加列的SQL语句
     */
    private String generateAddColumnSql(SimpleDataSource sourceDataSource, String tableName, String columnName, ExportSqlConfig config) {
        try {
            ColumnInfo columnInfo = CompareUtils.getColumnMeta(sourceDataSource, tableName, columnName);
            return String.format("ALTER TABLE %s ADD COLUMN %s;",
                    tableName,columnInfo.buildColumnDefinition());
        } catch (Exception e) {
            log.error("生成添加列SQL时发生错误: {}", e.getMessage(), e);
            return null;
        }
    }

    /**
     * 生成修改列的SQL语句
     */
    private String generateModifyColumnSql(SimpleDataSource sourceDataSource, String tableName, ColumnMismatch mismatch, ExportSqlConfig config) {
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE `").append(tableName).append("` MODIFY COLUMN `").append(mismatch.getColumnName()).append("` ");

        // 使用源数据库的类型
        if (mismatch.getSourceType() != null) {
            sql.append(mismatch.getSourceType());

            // 添加长度信息
            if (mismatch.getSourceLength() != null && mismatch.getSourceLength() > 0) {
                sql.append("(").append(mismatch.getSourceLength());
                if (mismatch.getSourceScale() != null && mismatch.getSourceScale() > 0) {
                    sql.append(",").append(mismatch.getSourceScale());
                }
                sql.append(")");
            }
        }

        // 添加可空性
        if (mismatch.getSourceNullable() != null) {
            if (!mismatch.getSourceNullable()) {
                sql.append(" NOT NULL");
            }
        }

        sql.append(";");

        return sql.toString();
    }
}
